package com.xxl.job.executor.service.impl;

import com.xxl.job.core.log.XxlJobLogger;
import com.xxl.job.executor.common.util.DBUtils;
import com.xxl.job.executor.common.util.SpringContextHolder;
import com.xxl.job.executor.service.IMonitorExecpuStatisService;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.sql.Connection;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

@Service("monitorExecpuStatisService")
public class MonitorExecpuStatisService implements IMonitorExecpuStatisService {
    Connection connPre = null;

    @Override
    public void transfer() throws Exception {
        XxlJobLogger.log("=====开始执行业务操作===");
        final String recordName = "etcboutlist_monitor_statis";

        try {
            DataSource dataSourcePre = SpringContextHolder.getBean("dataSourcePre");
            connPre = DataSourceUtils.getConnection(dataSourcePre);
            connPre.setAutoCommit(false);

            //统计ETC监视明细数据
            monitorStatisETC(connPre);
            //拆包待发送表
            analyzePackageNoSend(connPre);
            //将千方的流水对账交易量数据更新进来
            monitorStatisQFETC(connPre);

            connPre.commit();

            String sqlPro = "truncate table TB_STATIS_MONITOR_EXETCPU_TMP";
            DBUtils.execute(connPre, sqlPro);
            XxlJobLogger.log("[普通]重置临时表成功");

            connPre.commit();

            XxlJobLogger.log("=====完成执行业务操作===");
        }catch(Exception e){
            if (connPre != null) {
                try {
                    connPre.rollback();
                    XxlJobLogger.log("回滚成功.");
                }catch (Exception e1){
                    e1.printStackTrace();
                }
            }
            XxlJobLogger.log("抛出异常："+e.getMessage());
            throw e;
        }finally{
            if (connPre != null) {
                try {
                    connPre.setAutoCommit(true);
                    connPre.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
    }

    void monitorStatisETC(Connection conn) throws Exception {
        String sql = "MERGE INTO TB_STATIS_MONITOR_EXETCPU a " +
                "USING (select " +
                "TRADE_DATE,sum(QFSEND_STATE) PRO_SEND,sum(decode(RECV_STATE,0,1,1,0)) PRO_EXCEPTION," +
                "sum(decode(CLEAR_STATE,1,1,0)) CL_REMOTE,sum(decode(CLEAR_STATE,2,1,0)) CL_LOCAL," +
                "sum(decode(NOCLEAR_STATE,0,1,1,0,0)) CL_NONEED," +
                "sum(decode(ACC_STATE,1,1,0)) CT_CHECKED,sum(decode(ACC_STATE,2,1,0)) ISS_CHECKED " +
                "from TB_SYNC_MONITOR_EXETCPU_0 group by TRADE_DATE) b " +
                "ON (a.statis_date=b.TRADE_DATE) " +
                "WHEN MATCHED THEN " +
                "  UPDATE SET a.pro_send=b.PRO_SEND,a.pro_total=nvl(a.pro_nosend,0)-nvl(a.pro_exception,0)+b.PRO_EXCEPTION+b.PRO_SEND," +
                "  a.pro_nosend=nvl(a.pro_nosend,0)-nvl(a.pro_exception,0)+b.PRO_EXCEPTION,a.pro_exception=b.PRO_EXCEPTION," +
                "  a.cl_remote=b.CL_REMOTE,a.cl_local=b.CL_LOCAL,a.cl_noneed=b.CL_NONEED,a.cl_nosend=b.PRO_SEND-b.CL_REMOTE-b.CL_LOCAL-b.CL_NONEED," +
                "  a.iss_checked=b.ISS_CHECKED,a.iss_unchecked=b.CL_LOCAL-b.ISS_CHECKED," +
                "  a.ct_checked=b.CT_CHECKED,a.ct_unchecked=b.CL_REMOTE-b.CT_CHECKED,a.update_time=sysdate " +
                "WHEN NOT MATCHED THEN " +
                "  INSERT (a.SEQ_ID,a.STATIS_DATE,a.PRO_TOTAL,a.PRO_SEND,a.PRO_NOSEND,a.PRO_EXCEPTION," +
                "  a.CL_REMOTE,a.CL_LOCAL,a.CL_NONEED,a.CL_NOSEND," +
                "  a.ISS_CHECKED,a.ISS_UNCHECKED,a.CT_CHECKED,a.CT_UNCHECKED,a.INSERT_TIME,a.UPDATE_TIME) " +
                "  VALUES (SEQ_TB_STATIS_MONITOR_EXETCPU.nextval,b.TRADE_DATE,b.PRO_SEND+b.PRO_EXCEPTION,b.PRO_SEND,b.PRO_EXCEPTION,b.PRO_EXCEPTION," +
                "  b.CL_REMOTE,b.CL_LOCAL,b.CL_NONEED,b.PRO_SEND-b.CL_REMOTE-b.CL_LOCAL-b.CL_NONEED," +
                "  b.ISS_CHECKED,b.CL_LOCAL-b.ISS_CHECKED,b.CT_CHECKED,b.CL_REMOTE-b.CT_CHECKED,sysdate,sysdate) ";
        Integer count = DBUtils.execute(conn, sql);
        XxlJobLogger.log("[普通]本次统计监视数据(条):" + count);
    }

    void analyzePackageNoSend(Connection conn) throws Exception {
        String sql = "select JSONCONTENT from dealdata_exception where ERRORTYPE=3 and ERRORSENDINTF='BASIC_ETCBOUTLIST' " +
                "and ERRORTIME>=to_date('2020-04-20 00:00:00','yyyy-mm-dd hh24:mi:ss') ";
        List<Map<String, Object>> list = DBUtils.queryData(conn, sql);
        XxlJobLogger.log("[普通]待发送数据容器中总共含(条):" + list.size());

        Map<String, Integer> cntMap = DBUtils.statisJsonContentCnt(list);
        XxlJobLogger.log("[普通]待发送数据已统计完成日期个数为(条):" + cntMap.size());

        String strDate = "";
        int loop = 0;
        Integer nCnt = 0;
        Iterator<Map.Entry<String, Integer>> it = cntMap.entrySet().iterator();
        while (it.hasNext()) {
            //组装新的包名
            Map.Entry<String, Integer> entry = it.next();
            strDate = entry.getKey();
            if (strDate == null || strDate.isEmpty()) {
                continue;
            }
            nCnt = entry.getValue();

            sql = "insert into TB_STATIS_MONITOR_EXETCPU_TMP(statis_date,pro_nosend) " +
                  "values('"+strDate+"',"+nCnt+")";
            DBUtils.execute(conn, sql);

            ++loop;
        }
        XxlJobLogger.log("[普通]待发送数据日期临时数据已插入(条):" + loop);

        sql = "MERGE INTO TB_STATIS_MONITOR_EXETCPU a " +
                "USING TB_STATIS_MONITOR_EXETCPU_TMP b " +
                "ON (a.STATIS_DATE=b.STATIS_DATE) " +
                "WHEN MATCHED THEN " +
                "  update set a.PRO_TOTAL=b.PRO_NOSEND+nvl(a.PRO_EXCEPTION,0)+nvl(a.PRO_SEND,0),a.PRO_NOSEND=b.PRO_NOSEND+nvl(a.PRO_EXCEPTION,0),UPDATE_TIME=sysdate " +
                "WHEN NOT MATCHED THEN " +
                "  INSERT (a.SEQ_ID,a.STATIS_DATE,a.PRO_NOSEND,a.INSERT_TIME,a.UPDATE_TIME) " +
                "  VALUES (SEQ_TB_STATIS_MONITOR_EXETCPU.nextval,b.STATIS_DATE,b.PRO_NOSEND,sysdate,sysdate) ";
        Integer count = DBUtils.execute(conn, sql);
        XxlJobLogger.log("[普通]待发送数据已合并(条):" + count);

        sql = "update TB_STATIS_MONITOR_EXETCPU a set PRO_NOSEND=nvl(PRO_EXCEPTION,0),PRO_TOTAL=nvl(PRO_SEND,0)+nvl(PRO_EXCEPTION,0) where " +
                "not exists (select 1 from TB_STATIS_MONITOR_EXETCPU_TMP b where a.STATIS_DATE=b.STATIS_DATE) ";
        count = DBUtils.execute(conn, sql);
        XxlJobLogger.log("[普通]无待发送数据的待发送数更新为0(条):" + count);

        sql = "delete from TB_STATIS_MONITOR_EXETCPU_TMP ";
        count = DBUtils.execute(conn, sql);
        XxlJobLogger.log("[普通]本次清空待发送临时数据(条):" + count);
    }

    void monitorStatisQFETC(Connection conn) throws Exception {
        String sql = "MERGE INTO TB_STATIS_MONITOR_EXETCPU a \n" +
                "USING TB_CHARGE_MONITOR_INTF_RECV b \n" +
                "ON (a.STATIS_DATE=b.TRADEDATE) \n" +
                "WHEN MATCHED THEN \n" +
                "  UPDATE SET a.CL_REMOTE=b.diffEtcOutRecv,a.CL_LOCAL=b.localEtcOutRecv,\n" +
                "  a.ISS_CHECKED=b.localEtcOutAccount,a.CT_CHECKED=b.diffEtcOutAccount,a.SP_CHECKED=b.diffEtcOutSplit+b.localEtcOutSplit,\n" +
                "  a.ISS_UNCHECKED=b.localEtcOutUpload-b.localEtcOutAccount,a.CT_UNCHECKED=b.diffEtcOutUpload-b.diffEtcOutAccount,\n" +
                "  a.SP_UNCHECKED=b.diffEtcOutUpload+b.localEtcOutUpload-b.diffEtcOutSplit-b.localEtcOutSplit," +
                "  a.CL_NOSEND=a.PRO_SEND-b.diffEtcOutRecv-b.localEtcOutRecv-a.CL_NONEED ";
        Integer count = DBUtils.execute(conn, sql);
        XxlJobLogger.log("[普通]更新流水对账交易量(条):" + count);
    }
}
